EDA on KAMPALA CITY AUTHORITY's(KCCA) PROPERTY DATA¶
Table of Contents¶
- Problem Statement
- 1.1 Introduction
- 1.2 DataSource and DataSet
- 1.1 Introduction
- Importing Packages
- Loading Data
- Data Preprocessing
- Exploratory Data Analysis
1. Problem Statement¶
a. To check Quality of Data collected During Survey
b. How Various factors defined during Survey impacting the valuation of Property under Survey in same area?
c. Disparity in the Property Valaution method1.1. Introduction¶
Kampala City Authority, Uganda (KCCA) Completed Property Survey, in order to complete the valuation of all properties under them. Property Tax is levied by them on Valuation amount. Property tax is one of the major Revenue head for them. Here I reviewed and analyzed samples of assessed/valued properties in Central Division and used this data (Property attributes and Ratable Values).
During the data collection process, about 14-16 attributes/Factors are captured on each and every property. These attributes can be broadly categorized as details on; Ownership, property location, Usage, Building, Construction, Accommodation and others. It should be noted that although comprehensive data is collected on every property, not all this data is used to make assessments for property Values. Some data is needed/used for administrative purposes for example ownership details.
The purpose of this data analysis is to check Quality of data collected and impact of attributes/ factors on earlier valuation value by KCCA.
Further to this analysis KCCA want to derive model to calculate Valuation (ALV / RV) with the useful Attributes / Factors
Attributes considered / collected during Survey are
1. Total Built Up Area
2. Type of Access
3. Neighborhood status
4. Building condition
5. Availability of NWSC
6. Availability of Hydro Power
7. Type of walls
8. Type of floor finish
9. Type of sanitation facility
10. Availability of Parking space
11. Security measure
12. Type of boundary wall
13. Type of wall finishes
14. Type of ceiling
15. Ownership
16. Usage of the Property1.2. Data Source and Dataset¶
Data collected by KCCA during property Survey done during year 2017-18.
Sample data of one Division is taken for analysis out of 5 division.2. Importing Packages¶
In [1]:
import sys # Import packages
import pandas as pd
import seaborn as sns
import numpy as np
import matplotlib.pyplot as plt
from matplotlib.pyplot import pie, axis, show
%matplotlib inline
import warnings # Ignore warning related to pandas_profiling
warnings.filterwarnings('ignore')
pd.set_option('display.max_columns', 100) # Display all dataframe columns in outputs
In [2]:
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns
sns.set(style='whitegrid', font_scale=1.3, color_codes=True) # To apply seaborn styles to the plots.
In [3]:
# Making plotly specific imports
# These imports are necessary to use plotly offline without signing in to their website.
from plotly.offline import init_notebook_mode, iplot
import plotly.graph_objs as go
import chart_studio.plotly as py
from plotly import tools
init_notebook_mode(connected=True)
In [4]:
# Install chart_studio
!pip install chart-studio
3. Loading Data¶
For Data Visualisation here we are using 2 dataset of KCCA - Property Valuation.
1. Property Master Data - This Dataset contains details about property Ownership details, Property Physical address along with its Latitutde and Longitude details
2. Property Valuation Data - This Dataset contains details about property details which has impact on Valuation and on Revenue of KCCA3.1 Importing Two Datasets¶
Importing 1st Dataset - for Proeprty Details (Ownerhip, Area, Location Details)¶
In [5]:
pm = pd.read_csv("https://raw.githubusercontent.com/jaymalak/jaymala.kanagala-gmail.com-Term1-2-EDA-Project/master/DATA/Propmst.csv")
Importing 2nd Dataset - for Property Valuation Details - Carpet Area, Factors affecting valuation and Proeprty Tax(Revenue)¶
In [6]:
pv = pd.read_csv("https://raw.githubusercontent.com/jaymalak/jaymala.kanagala-gmail.com-Term1-2-EDA-Project/master/DATA/propval.csv")
In [7]:
pm.dtypes
Out[7]:
In [8]:
pv.dtypes
Out[8]:
In [9]:
pm.head()
Out[9]:
In [10]:
pm.shape
Out[10]:
In [11]:
pm.info()
3.2 Description of Dataset I¶
. This Data set contains total 27 Columns and total 7924 Property details (row)
. There are 7 Numeric and 20 columns with Object type
. There are missing values under following coloumn
1. Prop_org_name
2. Owner first, last and middle name
3. Mobile Number
4. Email IDsIn [12]:
pv.head()
Out[12]:
In [13]:
pv.info()
In [14]:
pv.shape
Out[14]:
3.3 Description of Dataset II¶
. This Data set contains total 22 Columns and total 7924 Property details (row) . There are 5 Numeric and 18 columns with Object type . There are no missing values
In [15]:
pm.describe(include = 'all')
Out[15]:
In [16]:
pv.describe(include= 'all')
Out[16]:
3.4 Pandas Profiling before Data Processing¶
In [17]:
import pandas_profiling # Get a quick overview for all the variables using pandas_profiling
profilepm = pandas_profiling.ProfileReport(pm)
profilepm.to_file(output_file="D:\Jaymala\P\Datascience\Project\jaymala.kanagala@gmail.com-TERM 1&2 EDA Project\Before DataProcessing Profiling output\out_pm.html")
pm.profile_report(title='Pandas Profiling before Data Preprocessing', style={'full_width':True})
Out[17]:
In [18]:
import pandas_profiling # Get a quick overview for all the variables using pandas_profiling
profilepv = pandas_profiling.ProfileReport(pv)
profilepv.to_file(output_file="D:\Jaymala\P\Datascience\Project\jaymala.kanagala@gmail.com-TERM 1&2 EDA Project\Before DataProcessing Profiling output\out_pv.html")
pv.profile_report(title='Pandas Profiling before Data Preprocessing', style={'full_width':True})
Out[18]:
In [19]:
pv_rd = pv.round(decimals=3)
pv_rd.head()
Out[19]:
In [20]:
pm['Perish'].value_counts() ### Perish wise Count of Properties
Out[20]:
In [21]:
pm.groupby(['Perish','Village'])['Village'].count()
Out[21]:
In [22]:
pm['Perish'].value_counts().plot(kind='pie',fontsize=10, autopct='%1.5f%%',figsize=(20,30), shadow=True, startangle=135, legend=True, cmap='summer')
plt.ylabel('Perish')
plt.title('Pie chart showing the proportion of each Perish value')
Out[22]:
3.5 Merging Two Datasets, for easier EDA¶
In [23]:
prop_merge = pd.merge(pm, pv, how='inner', left_on='Property_No._', right_on='Property_No._').drop(['CAMV_ID'], 1)
prop_merge.head()
Out[23]:
In [24]:
prop_merge.info()
After merging two datasets, New Dataframe prop_merge created. New Dataframe prop_merge has¶
1. total 47 Columns
2. Eleven Columns are Numeric and 36 columns are of Object type
3. Missing Values are in Prop_ORG_name, Owner's First, middle and last name, Mobile Number, Email In [25]:
prop_merge.describe()
Out[25]:
In [26]:
prop_merge.shape
Out[26]:
3.6 Pandas Profiling Before Data Processing on Merged DataSet¶
In [27]:
import pandas_profiling
In [28]:
befprof = prop_merge.profile_report(title='Pandas Profiling before Data Preprocessing')
befprof.to_file(output_file="D:\Jaymala\P\Datascience\Project\jaymala.kanagala@gmail.com-TERM 1&2 EDA Project\Before DataProcessing Profiling output\profiling_before_preprocessing_onmerged.html")
prop_merge.profile_report(title='Pandas Profiling before Data Preprocessing', style={'full_width':True})
Out[28]:
Observations from Pandas Profiling before Data Processing¶
DataSet Information¶
. Number of Variables - 47
. Number of Observations - 7924
. Missing Cells - 29978 (8.0%)
Variables Type¶
. Numeric 8
. Categorical 28
. Boolean 1
. Text 2
. Rejected 8
Other Analysis¶
- Occupancy Type has constant value
- Property_Org_name has missing values. Need to check with Property Type, as if property is other than Residential then value must be there. Else Wrong Property in Property type and same need to rectify as same has impact on property valuation
4. Data Processing¶
4.1 Data Processing¶
In [29]:
prop_merge['Perish'].value_counts()
Out[29]:
Observation :Kisenyi III, is repeated as data found in Lower and upper case¶
In [30]:
prop_merge.head()
Out[30]:
Property_is_Legal, Division,Owner name (fname, Mname,lname,tutle), Plot area,Plot_no are constant and not require for analysis as per Problem statement¶
In [31]:
prop_merge.drop(['Property_is_Legal', 'Division', 'Owner_Fname','Owner_Mname','Owner_Lname', 'Title', 'Plot_Area','Plot_No'], 1, inplace=True)
In [32]:
prop_merge.head()
Out[32]:
In [33]:
prop_merge.shape
Out[33]:
In [34]:
prop_merge = prop_merge.round(decimals=0)
prop_merge.head()
Out[34]:
In [35]:
prop_merge.dtypes
Out[35]:
In [36]:
prop_merge.Property_Tax = prop_merge.Property_Tax.astype(int)
prop_merge.dtypes
Out[36]:
In [37]:
prop_merge.isnull().sum()
Out[37]:
After reviewing data further, it is clear that Mobile number and email_id not require further for analysis, so dropping these 2 columns also¶
In [38]:
prop_merge.drop(['Mobile_No','Email_Id'], 1, inplace=True)
In [39]:
# To convert all Perish in upper case
prop_merge['Perish'] = prop_merge['Perish'].str.upper()
prop_merge['Perish'].value_counts()
Out[39]:
In [40]:
prop_merge.head()
Out[40]:
In [41]:
prop_merge.shape
Out[41]:
4.2 Pandas Profiling after Data Processing¶
In [42]:
# To output pandas profiling report to an external html file.
# Saving the output as profiling_after_preprocessing.html
aftprof = prop_merge.profile_report(title='Pandas Profiling after Data Preprocessing')
aftprof.to_file(output_file="D:\Jaymala\P\Datascience\Project\jaymala.kanagala@gmail.com-TERM 1&2 EDA Project\Before DataProcessing Profiling output\profiling_after_preprocessing.html")
prop_merge.profile_report(title='Pandas Profiling after Data Preprocessing', style={'full_width':True})
Out[42]:
5. Exploratory Data Analysis¶
5.1 Analysis based on Perish¶
5.1.1 Which Perish has maximum number of properties¶
In [43]:
plt.figure(figsize = (15, 6))
prop_merge.groupby('Perish')['Property_No._'].count().sort_values(ascending=False).plot(kind='bar', color='g')
plt.ylabel('Count')
Out[43]:
Observation :¶
1. KAMWOKYA II has maximum number of PRoperties5.1.2 Which Perish generate Highest Revenue for KCCA¶
In [44]:
plt.figure(figsize = (15, 6))
prop_merge.groupby('Perish')['Property_Tax'].sum().sort_values(ascending=False).plot(kind='bar', color='r')
plt.ylabel('Revenue')
Out[44]:
Observation :¶
- Civic Centre Generates Maximum Revenue for KCCA
5.2 Analysis based on Village¶
5.2.1 Which Villages has Maximum number of Properties¶
In [45]:
## Perish and Village wise Property Count
prop_merge.groupby(['Perish','Village'])['Village'].count()
Out[45]:
In [46]:
prop_merge.groupby(['Perish','Village'])['Property_No._'].count().sort_values(ascending=False)[:10]
Out[46]:
In [47]:
plt.figure(figsize = (15, 6))
prop_merge.groupby(['Perish','Village'])['Property_No._'].count().sort_values(ascending=False)[:10].plot(kind='bar', color='g')
plt.ylabel('Count')
Out[47]:
Observations :¶
1. Village name within Perish is repeating. Same Village name "Church Zone found in mutiple Perishes.
2. Village Kisenyi II Zone of KAMWOKYA II Perish ahs Maximum number of proeprties.
3. Civic City Zone which generating Maximum Revenue for KCCA has only 3 villages and count of Properties (269) is also less as compare to other Zone5.2.2 Which Village generates Maximum Revenue for KCCA¶
In [48]:
plt.figure(figsize = (15, 6))
prop_merge.groupby(['Perish','Village'])['Property_Tax'].sum().sort_values(ascending=False)[:10].plot(kind='bar', color='g')
plt.ylabel('Property Tax')
Out[48]:
Observations :¶
1. Villages from CIVIC centre genearting Maximum Revenue for KCCA.5.3. Analysis Based on Property Usage¶
5.3.1 Property Usage wise Count of Properties (Perish and Village wise)¶
In [49]:
prop_merge.groupby(['Perish','Village','Usage_Type'])['Property_No._'].count()
Out[49]:
5.3.2 Property Usage wise Revenue Generated for KCCA¶
In [50]:
plt.figure(figsize=(15,5))
prop_merge[prop_merge['Usage_Type'] =='RESIDENTIALRENTED'].groupby(['Perish'])['Property_Tax'].sum().plot(kind='bar',color='blue',)
prop_merge[prop_merge['Usage_Type'] !='RESIDENTIALRENTED'].groupby(['Perish'])['Property_Tax'].sum().plot(kind='bar', color='green',fontsize=13)
plt.ylabel('Property_Tax')
plt.legend(['Residential', 'Other thanResidential'])
Out[50]:
Observation :¶
1. Highest count of Properties as per data in KAMWOKYA II
2. But Higest Revenue is from Perish "Civic Center". All Properties Under Civic Centre are of Usage Type = "Other than Residential'
3. Other than Residential Properties generating Revenue for KCCA5.3.3 Top 10 combination of Perish -Village -Usage, which generates Highest Revenue for KCCA¶
In [51]:
plt.figure(figsize = (15, 6))
prop_merge.groupby(['Perish','Village','Usage_Type'])['Property_Tax'].sum().sort_values(ascending=False)[:10].plot(kind='bar', color='g')
plt.ylabel('Property Tax')
Out[51]:
5.3.4 Perish & Usage wise Property Tax¶
In [52]:
sns.catplot(x='Usage_Type', y='Property_Tax', hue='Perish', data=prop_merge, height=30, kind='bar',
palette='spring')
Out[52]:
Observation :¶
1. Commercial Properties has major impact on KCCA Revenue
2. CIVIC CNTER Perish and Christ The King Zone Village is Top to contribute Maximum Revenue with Commercial Properties
3. Special & Institutional Proeprties also showing spike in Property Tax as compare to other Usage type.5.4 Analysis of Mismatch Data¶
In [53]:
# checking data where Property Organisation is not NULL and usgae of Property is Residential.
# Residential Proeprty can have Owner Names but not Organisation name
is_commercial = prop_merge[(prop_merge.Property_Org_Name.notnull()) & (prop_merge.Usage_Type == 'RESIDENTIALRENTED')]
In [54]:
is_commercial.count() # Count of Commerical Properties where Organisation name in data not captured
Out[54]:
In [55]:
is_commercial.head()
Out[55]:
5.4.1 Perish wise count of mismatched Property Data¶
In [56]:
plt.figure(figsize = (15, 6))
is_commercial.groupby('Perish')['Property_No._'].count().sort_values(ascending=False).plot(kind='bar', color='g')
plt.ylabel('Count')
Out[56]:
Observations :¶
1. There are 503 record, where Usage is residential but organisation name is mentioned.
2. KCCA needs to rectify such records, as if Orgnisation name is there means Proeprty usage must be other than
Residential OR may be usage type wrongly entered.
3. KOLOLO IV has maximum such records.5.5 Analysis on the basis of Higest Property Tax¶
5.5.1 Top 10 Property Tax Payers¶
In [57]:
prop_merge.iloc[prop_merge['Property_Tax'].sort_values(ascending=False)[:10].index, :]
Out[57]:
5.5.2 Perish wise Revenue from Top 10 Properties¶
In [58]:
higest_taxpayer = prop_merge.iloc[prop_merge['Property_Tax'].sort_values(ascending=False)[:10].index, :]
plt.figure(figsize = (15, 6))
higest_taxpayer.groupby('Perish')['Property_Tax'].sum().sort_values(ascending=False).plot(kind='bar', color='g')
plt.title("Perishwise Revenue from Top 10 Properties")
plt.ylabel("Property Tax")
Out[58]:
In [59]:
print(higest_taxpayer.groupby('Perish')['Property_Tax'].sum())
5.5.3 Usage wise Revenue from Top 10 Properties¶
In [60]:
higest_taxpayer = prop_merge.iloc[prop_merge['Property_Tax'].sort_values(ascending=False)[:10].index, :]
plt.figure(figsize = (15, 6))
higest_taxpayer.groupby('Usage_Type')['Property_Tax'].sum().sort_values(ascending=False).plot(kind='bar', color='g')
plt.title("Usage wise Revenue from Top 10 Properties")
plt.ylabel("Property Tax")
Out[60]:
5.5.4 Analyis of Top 10 Properties on the basis of Carpet Area of Property¶
In [61]:
higest_taxpayer_carpetarea = prop_merge.iloc[higest_taxpayer['Carpet_Area_'].sort_values(ascending=False)[:10].index, :]
plt.figure(figsize = (15, 6))
higest_taxpayer.groupby('Carpet_Area_')['Property_Tax'].sum().plot(kind='bar', color='g')
plt.title("Carpet Area wise Revenue from Top 10 Properties")
plt.ylabel("Property Tax")
Out[61]:
In [62]:
higest_taxpayer_carpetarea.loc[:,['Carpet_Area_', 'Perish','Usage_Type','Avg._Property_Rate_(per_SQM)']]
Out[62]:
In [63]:
higest_taxpayer_carpetarea[higest_taxpayer_carpetarea['Property_No._'].isin(["P117014445","P117005985"])]
Out[63]:
Observations :¶
1. Highest Tax payer are from Perish Civic Center with total tax = 548377830 UGX
2. Higest Tax Payer are of Usage type = Commerical and Institutional only. Mainly Commercial properties are paying
higest tax
3. Within Highest tax Payer, proeprty with less carpet area paying more than property with more carpet area, even though property is of same Usage type(Commercial) and in the same area (Perish = Civic Center)
4. Property rate per Square meter is more, almost double, even though having carpet area less.
Carpet_Area_ Perish Usage_Type Avg._Property_Rate_(per_SQM) Property_No._
16300.0 CIVIC CENTER COMMERCIAL 6740.0 P117014445
7005.0 CIVIC CENTER COMMERCIAL 16160.0 P117005985
May be some other factor affecting the valuation process. Let's see What are the other factors, are they varying/ different for this properties.
futher it is observed that only Tyoe of wall - finish, Type of Ceiling are different beside Carpet Area.
RV is likely impacting due to Type of wall finish, type of ceiling, because of these factors RV is high eventhough Carpet Area is less.
5.6 Analysis of Property Valuation on the basis of various Factors of Properties¶
In [64]:
plt.figure(figsize=(20, 20))
sns.boxplot(data=prop_merge, x='Perish', y='RV', palette='viridis', width=1.0)
plt.xticks(rotation=90)
Out[64]:
Observations :¶
- Alsmost all Perishes have Outliners
- KCCA need to work on Property Valuation method, many properties are outliners.
In [65]:
factor_info =['Type_of_Access_(X2)','Neighbourhood_Status_(X3)','Building_Condition_(X4)','Type_of_Walls_(X5)','Type_of_Wall_finish_(X6)','Type_of_Floor_Finish_(X7)','Type_of_Ceiling_(X8)','NWSC_Availabilty_(X9)','Hydro_Power_Availabilty_(X10)','Type_of_Saniation_Facility_(X11)','Availabilty_of_Parking_Space_(X12)','Type_of_Security_(X13)','Type_of_Boundary_Wall_(X14)'
]
In [66]:
for value in factor_info:
plt.figure(figsize=(15,10))
prop_merge[prop_merge['Usage_Type'] =='RESIDENTIALRENTED'].groupby([value])['RV'].mean().plot(kind='bar',
color='blue',fontsize =13 ,)
prop_merge[prop_merge['Usage_Type'] =='COMMERCIAL'].groupby([value])['RV'].mean().plot(kind='bar', color='green',fontsize=13,)
prop_merge[prop_merge['Usage_Type'] =='INSTITUTIONAL'].groupby([value])['RV'].mean().plot(kind='bar', color='grey',fontsize=13,)
prop_merge[prop_merge['Usage_Type'] =='INDUSTRIAL'].groupby([value])['RV'].mean().plot(kind='bar', color='pink',fontsize=13,)
prop_merge[prop_merge['Usage_Type'] =='SPECIAL'].groupby([value])['RV'].mean().plot(kind='bar', color='red',fontsize=13,)
plt.ylabel('RV')
plt.xlabel(value)
plt.legend(['Residential', 'COMMERCIAL','INSTITUTIONAL','INDUSTRIAL', 'SPECIAL'])
Observations¶
- In Central Division, Majority Properties are Non-Residential (Other than Residential)
- Vauation of property as per data depend on various above factors
In [67]:
prop_new = prop_merge.copy(deep=True) # Creating New dataframe for correlation analysis, as need to drop few fields
In [68]:
prop_new.head()
Out[68]:
In [69]:
prop_new.drop(['House_No', 'House_/_Apartment_Name', 'Street_/_Road','Area_/_Locality','Property_Org_Name', 'Contact_No', 'COIN','Property_Address','Latitude','Longitude','Frontage','Property_Tax','Avg._Property_Rate_(per_SQM)'], 1, inplace=True)
In [70]:
prop_new.drop(['Property_Tax','Avg._Property_Rate_(per_SQM)'], 1, inplace=True)
In [99]:
prop_new.drop(['Property_Type','Property_SubType','Occupency_Type_'], 1, inplace=True)
In [101]:
prop_new.drop(['Occupency_Type_'], 1, inplace=True)
In [102]:
prop_new.head()
Out[102]:
In [103]:
from sklearn.preprocessing import LabelEncoder # Label encoder is used to transform non-numerical labels to numerical labels.
number = LabelEncoder() # Here as our data set consists only of categorical values except age
for i in prop_new.columns: # in order to find the correlation between variables, we do Label encoding
prop_new[i] = number.fit_transform(prop_new[i].astype('str'))
In [104]:
corr=prop_new.corr()['RV']
corr[np.argsort(corr,axis=0)[::-1]]
Out[104]:
In [106]:
features_correlation = prop_new.corr()
plt.figure(figsize=(15,15))
sns.heatmap(features_correlation,vmax=1,square=True,annot=False,cmap='Purples')
plt.show()
In [110]:
plt.figure(figsize=(25,25))
sns.heatmap( prop_new.corr(), annot=True );
plt.show()
In [107]:
prop_new.corr()
Out[107]:
Observations :¶
- ALV and RV expected highly correlated as per domain as RV is depend on ALV. But data shows that low Correleation
- Valuation (ALV and RV) are supposed to calculate based on reamining Factors mentioned. Butit clearly stating that factors not impacting valuation strongly.
- Many Factors showing -ve Correlation
6. Conclusions¶
1. Survey data of Central Division has given detailed about property valuation
2. Data has disparity, Logical mismatch in Data Observed which impacting Valuation of Data
3. Valuation RV as compare to various Factors is not correlated
4. Many Factors considered for Valuation showing -ve Correlation 6.1 Actionable Insights¶
1. In Order to increase the Revenue of KCCA,they need to cross check the data with factors collected properly.
2. KCCA need to check the Factors considered for Valuation, as many showing -ve Correlation between each other
2. Need to standardise the Valuation formula based on Various factors of Properties